package com.salary.utils;

import com.salary.utils.reader.DataReader;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

public class ExcelValidator {
    private static final Logger logger = LoggerFactory.getLogger(ExcelValidator.class);

    /**
     * 校验加工组表格式
     * 要求：Excel文件包含"分切组"和"卡板工"两个工作表
     * "分切组"表头需包含：名称、岗位、数量、兼职量、损耗量
     * "卡板工"表头需包含：名称、岗位、底板数量、盖板数量
     * @param filePath Excel文件路径
     * @return 校验结果，true为通过
     */
    public static boolean validateProcessingGroupTable(String filePath) {
        if (filePath == null || filePath.isEmpty()) {
            logger.error("加工组表格路径为空");
            return false;
        }

        logger.info("开始验证加工组表格文件: " + filePath);

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 查找"分切组"工作表
            Sheet cuttingSheet = null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet curSheet = workbook.getSheetAt(i);
                String sheetName = curSheet.getSheetName();

                if (sheetName.contains("分切组")) {
                    cuttingSheet = curSheet;
                    logger.info("找到分切组工作表: " + sheetName);
                    break;
                }
            }

            if (cuttingSheet == null) {
                logger.error("加工组表格中未找到分切组工作表");
                logger.info("工作簿中包含的工作表：");
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    logger.info(" - " + workbook.getSheetName(i));
                }
                return false;
            }

            // 查找表头
            boolean foundNameCol = false;
            boolean foundPositionCol = false;
            boolean foundQuantityCol = false;
            boolean foundPartTimeCol = false;
            boolean foundWasteCol = false;

            // 在前几行中查找表头
            for (int rowIndex = 0; rowIndex <= 5; rowIndex++) {
                Row row = cuttingSheet.getRow(rowIndex);
                if (row == null) continue;

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) continue;

                    String value = DataReader.getCellValueAsString(cell).trim();
                    if (value.contains("名称")) foundNameCol = true;
                    else if (value.contains("岗位")) foundPositionCol = true;
                    else if (value.contains("数量") && !value.contains("底板") && !value.contains("盖板")) foundQuantityCol = true;
                    else if (value.contains("兼职量")) foundPartTimeCol = true;
                    else if (value.contains("损耗量")) foundWasteCol = true;
                }

                // 如果在当前行找到了所有需要的列，就不再继续查找
                if (foundNameCol && foundPositionCol && foundQuantityCol && foundPartTimeCol && foundWasteCol) {
                    break;
                }
            }

            boolean isValid = foundNameCol && foundPositionCol && foundQuantityCol && foundPartTimeCol && foundWasteCol;

            if (isValid) {
                logger.info("加工组表格验证通过");
            } else {
                StringBuilder missingColumns = new StringBuilder("加工组表格中缺少必要的列: ");
                if (!foundNameCol) missingColumns.append("名称, ");
                if (!foundPositionCol) missingColumns.append("岗位, ");
                if (!foundQuantityCol) missingColumns.append("数量, ");
                if (!foundPartTimeCol) missingColumns.append("兼职量, ");
                if (!foundWasteCol) missingColumns.append("损耗量, ");
                logger.error(missingColumns.toString());
            }

            return isValid;

        } catch (IOException e) {
            logger.error("读取加工组表格文件失败: " + e.getMessage(), e);
            return false;
        }
    }
    /**
     * 验证工资资料表是否符合要求
     *
     * @param filePath 工资资料表文件路径
     * @return 验证结果，true表示工资资料表有效
     */
    public static boolean validateSalaryInfoTable(String filePath) {
        return validateExcelTable(filePath, new String[]{"姓名", "收承兑吨数"}, "业务员工资资料表");
    }

    /**
     * 验证水电租表是否符合要求
     *
     * @param filePath 水电租表文件路径
     * @return 验证结果，true表示水电租表有效
     */
    public static boolean validateUtilityRentTable(String filePath) {
        String[] requiredColumns = {"姓名", "租金", "水费", "热水费", "电费", "合计"};
        return validateExcelTable(filePath, requiredColumns, "水电租表");
    }

    /**
     * 验证系数表格式是否有效
     *
     * @param filePath 系数表文件路径
     * @return 是否为有效的系数表
     */
    public static boolean validateCoefficientTable(String filePath) {
        String[] requiredColumns = {"姓名", "系数"};
        return validateExcelTable(filePath, requiredColumns, "系数表");
    }

    /**
     * 验证个税表是否符合要求
     *
     * @param taxFilePath 个税表文件路径
     * @return 验证结果，true表示个税表有效
     */
    public static boolean validateTaxTable(String taxFilePath) {
        return ExcelValidator.validateExcelTable(taxFilePath, new String[]{"姓名", "个税"}, "个税表");
    }

    /**
     * 验证输出模板是否符合要求
     *
     * @param templatePath 模板文件路径
     * @return 验证结果，true表示模板有效
     */
    public static boolean validateOutputTemplate(String templatePath) {
        if (templatePath == null || templatePath.isEmpty()) {
            logger.error("模板路径为空");
            return false;
        }

        logger.info("开始验证模板文件: " + templatePath);

        try (FileInputStream fis = new FileInputStream(templatePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                logger.error("模板中未找到工作表");
                return false;
            }

            // 验证表格结构
            boolean hasNameHeader = false;
            boolean hasBonusColumn = false;

            // 检查前五行可能的表头
            for (int rowIndex = 0; rowIndex <= 4; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    if (cell == null) continue;

                    String cellValue = DataReader.getCellValueAsString(cell).trim();

                    // 检查姓名列
                    if (!hasNameHeader && (cellValue.contains("姓名") || cellValue.contains("名字") || cellValue.contains("名称") ||
                            cellValue.equals("姓名") || cellValue.equals("名字") || cellValue.equals("名称"))) {
                        hasNameHeader = true;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到姓名列: " + cellValue);
                    }

                    // 检查奖金列
                    if (!hasBonusColumn && (cellValue.contains("奖金") || cellValue.contains("金额") || cellValue.contains("奖") ||
                            cellValue.equals("总奖金") || cellValue.equals("奖金") || cellValue.equals("工资"))) {
                        hasBonusColumn = true;
                        logger.info("在第" + (rowIndex+1) + "行第" + (colIndex+1) + "列找到奖金列: " + cellValue);
                    }
                }
            }

            // 验证结果
            boolean isValid = hasNameHeader && hasBonusColumn;

            if (isValid) {
                logger.info("模板验证通过");
            } else {
                if (!hasNameHeader) {
                    logger.error("模板中未找到姓名列");
                }
                if (!hasBonusColumn) {
                    logger.error("模板中未找到奖金列");
                }
            }

            return isValid;

        } catch (IOException e) {
            logger.error("读取模板文件失败: " + e.getMessage(), e);
            return false;
        }
    }

    /**
     * 验证社保表是否符合要求
     *
     * @param socialInsuranceFilePath 社保表文件路径
     * @return 验证结果，true表示社保表有效
     */
    public static boolean validateSocialInsuranceTable(String socialInsuranceFilePath) {
        return validateExcelTable(socialInsuranceFilePath, new String[]{"姓名", "社保"}, "社保表");
    }

    /**
     * 验证每月扣款表是否符合要求
     *
     * @param deductionFilePath 扣款表文件路径
     * @return 验证结果，true表示扣款表有效
     */
    public static boolean validateMonthlyDeductionTable(String deductionFilePath) {
        return validateExcelTable(deductionFilePath, new String[]{"姓名", "金额"}, "扣款表");
    }

    /**
     * 验证考核得分表是否符合要求
     *
     * @param filePath 考核得分表文件路径
     * @return 验证结果，true表示考核得分表有效
     */
    public static boolean validateAssessmentScoreTable(String filePath) {
        return validateExcelTable(filePath, new String[]{"姓名", "分数"}, "考核得分表");
    }

    /**
     * 验证考勤表是否符合要求
     *
     * @param filePath 考勤表文件路径
     * @return 验证结果，true表示考勤表有效
     */
    public static boolean validateAttendanceTable(String filePath) {
        return validateExcelTable(filePath, new String[]{"姓名", "病假", "带薪假", "事假", "旷工", "迟到次数", "1.5倍", "2倍", "3倍", "实际出勤"},
                "考勤表");
    }
    /**
     * 通用的Excel表验证方法
     *
     * @param filePath 表格文件路径
     * @param requiredColumns 必须包含的列名数组
     * @param tableDesc 表格描述，用于日志信息
     * @return 验证结果，true表示表格有效
     */
    public static boolean validateExcelTable(String filePath, String[] requiredColumns, String tableDesc) {
        if (filePath == null || filePath.isEmpty()) {
            logger.error(tableDesc + "路径为空");
            return false;
        }

        logger.info("开始验证" + tableDesc + "文件: " + filePath);

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                logger.error(tableDesc + "中未找到工作表");
                return false;
            }

            logger.info("检查" + tableDesc + "工作表: " + sheet.getSheetName());

            // 使用通用方法查找必要的列
            Map<String, Integer> columnIndices = DataReader.findColumnIndices(sheet, requiredColumns);

            // 验证是否找到所有必要的列
            List<String> missingColumns = new ArrayList<>();
            for (String column : requiredColumns) {
                if (!columnIndices.containsKey(column)) {
                    missingColumns.add(column);
                }
            }

            boolean isValid = missingColumns.isEmpty();

            if (isValid) {
                logger.info(tableDesc + "验证通过");
            } else {
                for (String column : missingColumns) {
                    logger.error(tableDesc + "中未找到" + column + "列");
                }
            }

            return isValid;

        } catch (IOException e) {
            logger.error("读取" + tableDesc + "文件失败: " + e.getMessage(), e);
            return false;
        }
    }



    /**
     * 验证业务员出库表是否符合要求
     *
     * @param filePath 业务员出库表文件路径
     * @return 验证结果，true表示业务员出库表有效
     */
    public static boolean validateSalaryDataTable(String filePath) {
        return validateExcelTable(filePath, new String[]{"业务员", "数量", "吨纸毛利", "客户编码", "存货名称", "仓库"},
                "出库列表");
    }

}
